Release 10.1A: OpenEdge Development:
Progress Dynamics Basic Development

Database triggers and Progress Dynamics

SmartDataobjects are intended to represent a single point-of-update for each database table. As such, it is theoretically possible to enforce all referential integrity within the SDO itself and avoid using database trigger procedures.

However, avoiding triggers is not necessarily wise. Sometimes you might have to write low-level AppServer routines to update the database directly without going through an SDO. It is imperative that you maintain referential integrity (RI) constraints in these circumstances.

The biggest argument against database triggers is that they are not easy to maintain or document. However, with the use of relational design tools, you can overcome both of these problems.

If you are using the ERwin modeling tool with Progress Dynamics, you should retain the referential integrity checks automatically generated in the DELETE and WRITE triggers. These checks include code to control deletion cascade and restriction of records. In other cases, where the database schema is not generated using ERwin and all the Progress Dynamics naming conventions are not necessarily observed, then you can write trigger procedures that do basic referential integrity checks. If your application already includes such procedures, then you can keep them.

Where a delete restrict is conditional (for example, you cannot delete the Order if it has OrderLine records unless you are also deleting the Customer), the Customer SDO should delete OrderLine records on delete of the Customer. Thus, once the Customer DELETE event occurs in the database, the Orders remain but without any OrderLine records. In this way the RI constraint (on parent delete restrict) is enforced in all other circumstances.

Where you must make customizations to triggers outside of referential integrity, you should almost always make such changes in the SDO itself. You should not have to edit ERwin-generated triggers.

In summary, where database trigger procedures are concerned, it is good practice to restrict their behavior to essential referential integrity checks. Any more complex validation constitutes application business logic, and you should move it up to the application logic procedures that are more visible, more maintainable, and more flexible.

In addition, in those cases where the logic is an RI check that will result in an error if the check fails (a CAN-FIND lookup, for example), it is good practice to duplicate even that minimal logic that goes into database triggers in the application logic procedures. In this way you can expect that an update to the database made through Progress Dynamics application components will never fail at the level of the database triggers, because the same check will have been made higher up. This prevents you from worrying about defining and capturing error conditions and error messages generated by trigger procedures. In particular, it is essential to keep in mind that trigger procedures execute on the AppServer when your application runs in a distributed environment. If a trigger procedure generates an error message using the Progress 4GL MESSAGE statement, that message will never be seen on the client.

Also, keep in mind that the use of controls, such as Progress Dynamics combos and lookups, virtually assures that basic referential integrity checks will not fail, at least when updates are done through the application components you build, because the user will be forced to select a valid value for a key field from a list generated dynamically from the database.